﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 标签表操作类
    /// </summary>
    public class TermDAO : ITerm
    {
        /// <summary>
        /// 向标签表中插入一行新的数据, 返回插入数据的主键ID, 插入失败返回-1
        /// </summary>
        /// <param name="term">要插入的标签对象</param>
        /// <returns>返回插入数据的主键ID</returns>
        public int Insert(Term term)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Terms]
                                    (
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    )
                                    OUTPUT inserted.[term_id]
                                    VALUES 
                                    (
                                        @Name,
                                        @Slug,
                                        @Type,
                                        @Count,
                                        @ParentId,
                                        @Description
                                    );";
            try
            {
                return (int)SqlHelper.ExecuteScalar(sql,
                       new SqlParameter("@Name", term.Name),
                       new SqlParameter("@Slug", term.Slug.Trim().ToLower()),
                       new SqlParameter("@Type", term.Type),
                       new SqlParameter("@Count", term.Count),
                       new SqlParameter("@ParentId", term.ParentId),
                       new SqlParameter("@Description", string.IsNullOrEmpty(term.Description) ? System.DBNull.Value : (object)term.Description));
            }
            catch
            {
                return -1;
            }
        }
        /// <summary>
        /// 向标签表中批量插入多行新的数据, 返回插入数据的主键ID数组, 插入失败返回-1
        /// </summary>
        /// <param name="term">要插入的标签对象集合</param>
        /// <returns>返回插入数据的主键ID数组, 插入失败返回-1</returns>
        public int[] Insert(List<Term> termList)
        {
            string format = @"INSERT INTO [&#WednTablePrefix%&_Terms]
                                        (
                                            [term_name],
                                            [term_slug],
                                            [term_type],
                                        [term_count],
                                            [term_parent_id],
                                            [term_desc]
                                        )
                                        OUTPUT inserted.[term_id]
                                       VALUES 
                                        (
                                            '{0}',
                                            '{1}',
                                            {2},
                                            {3},
                                            {4},
                                            '{5}'
                                        );";
            StringBuilder sbd = new StringBuilder();
            foreach (var item in termList)
            {
                sbd.AppendFormat(format, item.Name, item.Slug, item.Type, item.Count, item.ParentId, item.Description);
            }
            DataSet ds = SqlHelper.ExecuteDataSet(sbd.ToString());
            int[] res = new int[ds.Tables.Count];
            for (int i = 0; i < res.Length; i++)
            {
                try
                {
                    res[i] = (int)ds.Tables[i].Rows[0][0];
                }
                catch
                {
                    res[i] = -1;
                }
            }
            return res;
        }

        /// <summary>
        /// 根据标签ID删除对应的标签记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除记录的ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Terms] WHERE [term_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据标签ID数组删除对应的标签记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除记录的ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            string ps = string.Join(",", ids);
            string sql = "DELETE FROM [&#WednTablePrefix%&_Terms] WHERE [term_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新一个完整的标签对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="term">要更新的对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateTerm(Term term)
        {
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Terms]
                                   SET
                                        [term_name]=@Name,
                                        [term_slug]=@Slug,
                                        [term_type]=@Type,
                                        [term_count]=@Count,
                                        [term_parent_id]=@ParentId,
                                        [term_desc]=@Description
                                   WHERE
                                        [term_id]=@Id;";
            return ExecuteNonQuery(term, sql);
        }

        /// <summary>
        /// 更新一个标签常用信息, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">标签ID</param>
        /// <param name="name">更新的名称(不更新则传入null)</param>
        /// <param name="slug">更新的别名(不更新则传入null)</param>
        /// <param name="parentId">更新的父ID(不更新则传入-1)</param>
        /// <param name="description">更新的描述(不更新则传入null)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateInfo(int id, string name, string slug, int parentId, string description)
        {
            List<string> strs = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            bool flag = false;
            if (!string.IsNullOrEmpty(name))
            {
                flag = true;
                strs.Add("[term_name]=@Name");
                ps.Add(new SqlParameter("@Name", name));
            }
            if (!string.IsNullOrEmpty(slug))
            {
                flag = true;
                strs.Add("[term_slug]=@Slug");
                ps.Add(new SqlParameter("@Slug", slug));
            }
            if (parentId != -1)
            {
                flag = true;
                strs.Add("[term_parent_id]=@ParentId");
                ps.Add(new SqlParameter("@ParentId", parentId));
            }
            if (!string.IsNullOrEmpty(description))
            {
                flag = true;
                strs.Add("[term_desc]=@Description");
                ps.Add(new SqlParameter("@Description", description));
            }
            if (flag)
            {
                //SQL语句有效
                string sql = @"UPDATE [&#WednTablePrefix%&_Terms]
                                        SET
                                            {0}
                                        WHERE 
                                            [term_id]=@Id;";
                sql = string.Format(sql, string.Join(",", strs));
                ps.Add(new SqlParameter("@Id", id));
                return SqlHelper.ExecuteNonQuery(sql, ps.ToArray());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 批量更新标签父ID
        /// </summary>
        /// <param name="ids">标签ID数组</param>
        /// <param name="parentId">更新的父ID</param>
        /// <returns></returns>
        public int UpdateParentId(int[] ids, int parentId)
        {
            string ps = string.Join(",", ids);
            string sql = @"UPDATE 
                                        [&#WednTablePrefix%&_Terms]
                                   SET
                                        [term_parent_id]=@ParentId
                                   WHERE
                                        [term_id] IN (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 根据标签ID更新标签下项目数, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要更新的标签ID</param>
        /// <param name="addCount">增加的数量(一般为1)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateCount(int id, int addCount)
        {
            string sql = @"UPDATE
                                        [&#WednTablePrefix%&_Terms]
                                    SET
                                        [term_count]=[term_count]+@AddCount
                                    WHERE
                                        [term_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@AddCount", addCount),
                new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据标签ID重新统计标签下项目数, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要更新的标签ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateCount(int id)
        {
            string sql = @"UPDATE
                                        [&#WednTablePrefix%&_Terms]
                                    SET
                                        [term_count]=(select COUNT(1) from [&#WednTablePrefix%&_Relations] where term_id=@Id)
                                    WHERE
                                        [term_id]=@Id";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 重新统计标签下项目数, 返回执行结果受影响行数
        /// </summary>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateCount()
        {
            string idSql = "select [term_id] from [&#WednTablePrefix%&_Terms]";
            string sqlFormat = @"UPDATE
	                                            [&#WednTablePrefix%&_Terms]
                                            SET
	                                            [term_count]=(select COUNT(1) from [&#WednTablePrefix%&_Relations] where term_id={0})
                                            WHERE
	                                            [term_id]={0}";
            int result = 0;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(idSql))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        result += SqlHelper.ExecuteNonQuery(string.Format(sqlFormat, id));
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("term_id", "&#WednTablePrefix%&_Terms");
        }

        /// <summary>
        /// 检查表中是否已经存在该别名, 返回一个布尔值, 存在则为True; 不存在则为False
        /// </summary>
        /// <param name="slug">要确定的别名</param>
        /// <returns>存在返回True; 不存在返回False</returns>
        public bool Exists(string slug)
        {
            return SqlHelper.ColumnExistsValue("&#WednTablePrefix%&_Terms", "term_slug", slug);
        }

        /// <summary>
        /// 选取标签总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)(不能使用参数, 要使用参数化写重载)</param>
        /// <returns>总数</returns>
        public int SelectTermsCount(string where)
        {
            string sql = @"SELECT
                                    COUNT
                                    (
                                        1
                                    )
                                   FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                   {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 选取标签总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)(不能使用参数, 要使用参数化写重载)</param>
        /// <returns>总数</returns>
        private int SelectTermsCount(string where, SqlParameter[] parameters)
        {
            string sql = @"SELECT
                                    COUNT
                                    (
                                        1
                                    )
                                   FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                   {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql, parameters);
        }

        /// <summary>
        /// 根据类型选取标签总数
        /// </summary>
        /// <param name="termType">标签类型(0:post_tag, 1:post_category..)</param>
        /// <returns>总数</returns>
        public int SelectTermsCountByType(int termType)
        {
            string sql = @"SELECT
                                    COUNT
                                    (
                                       1
                                    )
                                   FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                        [term_type]=@Type;";
            return (int)SqlHelper.ExecuteScalar(sql, new SqlParameter("@Type", termType));
        }

        /// <summary>
        /// 取出当前标签表中的所有标签对象集合, 返回标签对象集合
        /// </summary>
        /// <returns>标签对象集合</returns>
        public List<Term> SelectTermList()
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [&#WednTablePrefix%&_Terms];";
            return Select(sql);
        }

        /// <summary>
        /// 根据类型选取标签对象集合, 不存在则返回null
        /// </summary>
        /// <param name="termType">标签类型(0:post_tag, 1:post_category..)</param>
        /// <returns>标签对象</returns>
        public List<Term> SelectTermList(int termType)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                        [term_type]=@Type;";
            return Select(sql, new SqlParameter("@Type", termType));
        }

        /// <summary>
        /// 根据类型和父ID选取标签对象集合, 不存在则返回null
        /// </summary>
        /// <param name="termType">标签类型(0:post_tag, 1:post_category..)</param>
        /// <param name="parentId">父ID</param>
        /// <returns>标签对象</returns>
        public List<Term> SelectTermList(int termType, int parentId)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                        [term_type]=@Type
                                   AND
                                        [term_parent_id]=@ParentId;";
            return Select(sql,
                new SqlParameter("@Type", termType),
                new SqlParameter("@ParentId", parentId));
        }

        /// <summary>
        /// 根据对象ID和类型选取标签对象集合, 不存在则返回null
        /// </summary>
        /// <param name="objectId">对象ID</param>
        /// <param name="objectType">对象类型(0:Post, 1:User, 2:Link, 3:Comment)</param>
        /// <returns>标签对象</returns>
        public List<Term> SelectTermListByObject(int objectId, int objectType)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                        [term_id]
                                    IN
                                    (
                                    SELECT [term_id] FROM &#WednTablePrefix%&_Relations WHERE [object_id]=@ObjectId AND [object_type]=@ObjectType
                                    )";
            return Select(sql,
                new SqlParameter("@ObjectId", objectId),
                new SqlParameter("@ObjectType", objectType));
        }

        /// <summary>
        /// 根据ID选取标签对象, 不存在则返回null
        /// </summary>
        /// <param name="termid">标签ID</param>
        /// <returns>标签对象</returns>
        public Term SelectTerm(int termid)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                        [term_id]=@Id;";
            List<Term> list = Select(sql, new SqlParameter("@Id", termid));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 根据ID和类型选取标签对象, 不存在则返回null
        /// </summary>
        /// <param name="slug">标签别名</param>
        /// <returns>标签对象</returns>
        public Term SelectTerm(string slug)
        {
            string sql = @"SELECT 
                                        [term_id],
                                        [term_name],
                                        [term_slug],
                                        [term_type],
                                        [term_count],
                                        [term_parent_id],
                                        [term_desc]
                                    FROM
                                        [&#WednTablePrefix%&_Terms]
                                   WHERE
                                        [term_slug]=@Slug;";
            List<Term> list = Select(sql, new SqlParameter("@Slug", slug));
            return list != null ? list[0] : null;
        }

        #region select
        /// <summary>
        /// 私有选取数据方法
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Term> Select(string sql, params SqlParameter[] parameter)
        {
            List<Term> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<Term>();
                    while (reader.Read())
                    {
                        Term term = new Term()
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Slug = reader.GetString(2),
                            Type = reader.GetInt32(3),
                            Count = reader.GetInt32(4),
                            ParentId = reader.GetInt32(5),
                            Description = reader.IsDBNull(6) ? null : reader.GetString(6)
                        };
                        list.Add(term);
                    }
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有方法执行写入数据库操作
        /// </summary>
        /// <param name="term">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteNonQuery(Term term, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Name", term.Name),
                new SqlParameter("@Slug", term.Slug.Trim().ToLower()),
                new SqlParameter("@Type", term.Type),
                new SqlParameter("@Count", term.Count),
                new SqlParameter("@ParentId", term.ParentId),
                new SqlParameter("@Description", string.IsNullOrEmpty(term.Description) ? System.DBNull.Value : (object)term.Description),
                new SqlParameter("@Id", term.Id)
                );
        }
        #endregion
    }
}
